package com.zhiyou100.test.test01.dbutil;

import com.zhiyou100.util.mysql.C3p0Util;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yang
 * @className MyQueryRunner
 * @packageName com.zhiyou100.test.test01.dbutil
 * @Description TODO 我的 QueryRunner
 * @date 2020-08-06 16:23
 */
public class MyQueryRunner {
    private static final DataSource SOURCE = C3p0Util.DATA_SOURCE;

    public static <T> T queryOneObj(String sql, Class<T> t, Object... params) throws Exception {
        // 1. 获取链接
        Connection con = SOURCE.getConnection();

        // 2. 获取预编译对象，关联 sql 模版
        PreparedStatement sta = con.prepareStatement(sql);

        for (int i = 1; i <= params.length; i++) {
            // 3. 给占位符赋值
            sta.setObject(i, params[i - 1]);
        }

        // 3. 执行 sql 语句
        ResultSet resultSet = sta.executeQuery();

        T newInstance = null;

        while (resultSet.next()) {
            // 4. 创建 对象
            newInstance = t.newInstance();
            // 5. 获取 结果集的元数据 对象
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 6 根据元数据对象 获取列的个数
            int columnCount = metaData.getColumnCount();
            // 7 根据列数 进行循环遍历给对象进行赋值
            for (int i = 1; i <= columnCount; i++) {
                // 8 获取列的别名
                String columnLabel = metaData.getColumnLabel(i);
                // 9 根据列的别名去实例中寻找属性，并获取对应的 field 对象
                Field declaredField = newInstance.getClass().getDeclaredField(columnLabel);
                // 10 暴力访问
                declaredField.setAccessible(true);
                // 11 设置
                declaredField.set(newInstance, resultSet.getObject(i));
            }
        }

        // 12 关闭
        resultSet.close();
        sta.close();
        con.close();

        return newInstance;
    }

    public static Object queryOne(String sql, Class cla, Object[] arr) throws Exception {

        // 获取链接
        Connection con = SOURCE.getConnection();

        // 获取预编译对象，关联 sql 模版
        PreparedStatement sta = con.prepareStatement(sql);


        for (int i = 0; i < arr.length; i++) {
            // 给占位符 赋值
            sta.setObject(i + 1, arr[i]);
        }

        // 执行 execute 方法
        ResultSet set = sta.executeQuery();

        // 记录返回的对象
        Object object = null;

        while (set.next()) {
            // 通过反射创建对象
            object = cla.newInstance();

            // 对象的属性 需要和结果集同名的列的值保持一致
            ResultSetMetaData metaData = set.getMetaData();

            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                // 获取列名
                String name = metaData.getColumnLabel(i);
                // 获取当前行，指定列的值
                Object value = set.getObject(name);
                // 通过列名获取，同名的属性对象
                Field field = cla.getDeclaredField(name);
                // 暴力访问
                field.setAccessible(true);
                // 设置值
                field.set(object, value);
            }
        }

        // 关闭链接
        set.close();
        sta.close();
        con.close();

        // 返回值
        return object;
    }

    public static int update(String sql, Object... params) throws Exception {
        // 获取连接
        Connection connection = SOURCE.getConnection();

        // 获取预编译对象，关联 sql 模版
        PreparedStatement statement = connection.prepareStatement(sql);

        for (int i = 1; i <= params.length; i++) {
            // 给占位符赋值
            statement.setObject(i, params[i - 1]);
        }

        // 执行 execute 方法
        int i = statement.executeUpdate();

        // 关流
        statement.close();
        connection.close();

        return i;
    }

    public static int update(String sql) throws Exception {
        Connection con = SOURCE.getConnection();
        //获取连接
        PreparedStatement sta = con.prepareStatement(sql);
        //获取预编译对象 关联slq模板
        int hang = sta.executeUpdate();
        //执行execute方法
        //关闭连接
        sta.close();
        con.close();
        return hang;
    }

    public static List queryList(String sql, Class cla) throws Exception {
        // 1. 获取链接
        Connection con = SOURCE.getConnection();

        // 2. 获取预编译对象，关联 sql 模版
        PreparedStatement sta = con.prepareStatement(sql);

        ResultSet set = sta.executeQuery();

        List list = new ArrayList<>();

        while (set.next()) {
            Object o = cla.newInstance();
            ResultSetMetaData metaData = set.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnLabel = metaData.getColumnLabel(i);
                Field field = o.getClass().getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(o, set.getObject(i));
            }
            list.add(o);
        }


        return list;
    }

    public static List queryList(String sql, Class cla, Object... params) throws Exception {
        // 1. 获取链接
        Connection con = SOURCE.getConnection();

        // 2. 获取预编译对象，关联 sql 模版
        PreparedStatement sta = con.prepareStatement(sql);



        for (int i = 1; i <= params.length; i++) {
            // 3. 给占位符赋值
            sta.setObject(i, params[i - 1]);
        }

        ResultSet set = sta.executeQuery();

        List list = new ArrayList<>();



        while (set.next()) {
            Object o = cla.newInstance();
            ResultSetMetaData metaData = set.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnLabel = metaData.getColumnLabel(i);
                Field field = o.getClass().getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(o, set.getObject(i));
            }
            list.add(o);
        }


        return list;
    }

}
